﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using WL_Store.Model;

namespace WL_Store.DAL
{
   public class RefundDAL
    {
        //实例化退款信息实体模型
       //protected RefundModel model = new RefundModel();
        /// <summary>
        /// 查询所有退款信息或根据检索的信息查找信息
        /// </summary>
        /// <returns></returns>
        public List<RefundModel> GetRefunds(RefundSearchModel searchModel) 
        {
            //连表查询字符串
            string sql = @"select s.orderID,s.commodityId,s.comName,s.categoryId,g.category,s.transactionAmount,s.tradingHour,s.refundMoney,s.refundAmount,s.[state],s.explain,s.isDelete from SalesReturn as s inner join GoodsType as g on s.categoryId=g.categoryId";
            //构造where的泛型集合
            List<string> listwhere = new List<string>();
            //构造检索信息的实体模型的泛型集合
            List<SqlParameter> listparms = new List<SqlParameter>();
            //判断多条件查询的条件商品名称是否输入
            if (!string .IsNullOrWhiteSpace(searchModel.comName))
            {
                listwhere.Add("s.comName=@comName");
                listparms.Add(new SqlParameter("@comName", searchModel.comName));
            }
            //判断多条件查询的条件交易时间(开始)是否输入
            if (searchModel.StarttradingHour.HasValue)
            {
                listwhere.Add("s.tradingHour>=@StarttradingHour");
                listparms.Add(new SqlParameter("@StarttradingHour", searchModel.StarttradingHour));
            }
            //判断多条件查询的条件交易时间(结束)是否输入
            if (searchModel.EndtradingHour.HasValue)
            {
                listwhere.Add("s.tradingHour<=@EndtradingHour");
                listparms.Add(new SqlParameter("@EndtradingHour", searchModel.EndtradingHour));
            }
            //判断多条件查询的条件商品类别是否>0
            if (searchModel.categoryId>0)
            {
                listwhere.Add("s.categoryId=@categoryId");
                listparms.Add(new SqlParameter("@categoryId", searchModel.categoryId));
            }
            //判断多条件查询的条件退款状态是否>0
            if (searchModel.state > -1)
            {
                listwhere.Add("s.state=@state");
                listparms.Add(new SqlParameter("@state", searchModel.state));
            }
            //判断检索的退款的商品信息是否为空
            if (listwhere.Count>0)
            {
                sql += " where " + string.Join(" and " ,listwhere.ToArray());
            }


            //调用Dbhelper中的ExecuteReader方法
            SqlDataReader reader = Dbhelper.ExecuteReader(sql,listparms.ToArray());
            //构造一个关于退款信息实体模型的泛型集合
            List<RefundModel> list = new List<RefundModel>();
            //判断游标中是否有数据行
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    //实例化退款信息实体模型
                    RefundModel model = new RefundModel();
                    model.orderID = reader["orderID"].ToString();
                    model.commodityId = Convert.ToInt32(reader["commodityId"]);
                    model.comName = reader["comName"].ToString();
                    model.categoryId = Convert.ToInt32(reader["categoryId"]);
                    model.category = reader["category"].ToString();
                    model.transactionAmount = Convert.ToDouble(reader["transactionAmount"]);
                    model.tradingHour = Convert.ToDateTime(reader["tradingHour"]);
                    model.refundMoney= Convert.ToDouble(reader["refundMoney"]);
                    model.refundAmount = Convert.ToDouble(reader["refundAmount"]);
                    model.state = Convert.ToBoolean(reader["state"]);
                    model.explain = reader["explain"].ToString();
                    model.isDelete = Convert.ToBoolean(reader["isDelete"]);
                    //追加到退款信息实体模型的泛型集合中
                    list.Add(model);
                }
                //关闭游标
                reader.Close();
            }
            return list;
        }

        /// <summary>
        /// 查询商品类别
        /// </summary>
        /// <returns></returns>
        public DataTable GetGoodsType()
        {
            string sql = @"select * from GoodsType";

            return Dbhelper.ExecuteTable(sql);
        }

        /// <summary>
        /// 软删除退款信息 如果返回true：删除成功；返回false删除失败
        /// </summary>
        /// <param name="orderID"></param>
        /// <returns></returns>
        public bool DeleteRefund(string orderID)
        {
            string sql = @"update SalesReturn set isDelete=1 where orderID=@orderID";
            SqlParameter[] parameters = { 
            new SqlParameter("@orderID",orderID)
            };

            return Dbhelper.ExecuteNonQuery(sql,parameters)>0;
        }

        /// <summary>
        /// 添加退款信息 返回true：添加成功；返回false：添加失败
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool AddRefund(RefundModel model)
        {
            string sql = @"insert SalesReturn(orderID,commodityId,comName,categoryId,transactionAmount,tradingHour,refundMoney,refundAmount,[state],explain,isDelete) values
                        (@orderID,@commodityId,@comName,@categoryId,@transactionAmount,@tradingHour,@refundMoney,@refundAmount,@state,@explain,@isDelete)";

            SqlParameter[] parameters =
                { 
                new SqlParameter("@orderID",model.orderID),
                new SqlParameter("@commodityId",model.commodityId),
                new SqlParameter("@comName",model.comName),
                new SqlParameter("@categoryId",model.categoryId),
                new SqlParameter("@transactionAmount",model.transactionAmount),
                new SqlParameter("@tradingHour",model.tradingHour),
                new SqlParameter("@refundMoney",model.refundMoney),
                new SqlParameter("@refundAmount",model.refundAmount),
                new SqlParameter("@state",model.state),
                new SqlParameter("@explain",model.explain),
                new SqlParameter("@isDelete",model.isDelete)
            };

            return Dbhelper.ExecuteNonQuery(sql, parameters) > 0;
        }

        /// <summary>
        /// 根据订单编号查询退款信息
        /// </summary>
        /// <param name="orderID"></param>
        /// <returns></returns>
        public RefundModel GetRefund(string orderID)
        {
            string sql = @"select*from SalesReturn where orderId=@orderId";

            SqlParameter[] parameters = { 
            new SqlParameter("@orderId",orderID)
            };
            //调用Dbhelper中的ExecuteReader方法
            SqlDataReader reader = Dbhelper.ExecuteReader(sql,parameters);
           
            //判断游标中是否有数据行
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    //实例化退款信息实体模型
                    RefundModel model = new RefundModel();
                    model.orderID = reader["orderID"].ToString();
                    model.commodityId = Convert.ToInt32(reader["commodityId"]);
                    model.comName = reader["comName"].ToString();
                    model.categoryId = Convert.ToInt32(reader["categoryId"]);
                    //model.category = reader["category"].ToString();
                    model.transactionAmount = Convert.ToDouble(reader["transactionAmount"]);
                    model.tradingHour = Convert.ToDateTime(reader["tradingHour"]);
                    model.refundMoney = Convert.ToDouble(reader["refundMoney"]);
                    model.refundAmount = Convert.ToDouble(reader["refundAmount"]);
                    model.state = Convert.ToBoolean(reader["state"]);
                    model.explain = reader["explain"].ToString();
                    model.isDelete = Convert.ToBoolean(reader["isDelete"]);
                    //追加到退款信息实体模型的泛型集合中
                    return  model;
                }
                //关闭游标
                reader.Close();
            }
            return null;
        }

        /// <summary>
        /// 根据订单编号更改信息
        /// </summary>
        /// <param name="orderID"></param>
        /// <returns></returns>
        public bool UpdateRefund(RefundModel model)
        {
            string sql = @"update SalesReturn set commodityId=@commodityId,comName=@comName,categoryId=@categoryId,transactionAmount=@transactionAmount,tradingHour=@tradingHour,refundMoney=@refundMoney,refundAmount=@refundAmount,state=@state,explain=@explain  where orderID=@orderID";

            SqlParameter[] parameters =
                {
            new SqlParameter("@commodityId",model.commodityId),
            new SqlParameter("@comName",model.comName),
            new SqlParameter("@categoryId",model.categoryId),
            new SqlParameter("@transactionAmount",model.transactionAmount),
            new SqlParameter("@tradingHour",model.tradingHour),
            new SqlParameter("@refundMoney",model.refundMoney),
            new SqlParameter("@refundAmount",model.refundAmount),
            new SqlParameter("@state",model.state),
            new SqlParameter("@explain",model.explain),
            new SqlParameter("@orderID",model.orderID)
            };
            return Dbhelper.ExecuteNonQuery(sql,parameters)>0;
        }
    }
}
